﻿-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[spClearAllOwnerData] @OwnerID int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	declare @PersonID table (PersonID int primary key)
	insert into @PersonID (PersonID)
	select T1.PersonID
	from tblCustomer T1
	where T1.OwnerID = @OwnerID
	union select T2.ShipTo_PersonID
	from tblCustomer T1
		join tblOrderHeader T2 on T1.CustomerID = T2.CUstomerID
	where T1.OwnerID = @OwnerID
	union select PersonID
	from tblAccount 
	where OwnerID = @OwnerID
		and PersonID is not null

	declare @AddressID table (AddressID int primary key)
	insert into @AddressID (AddressID)
	select T2.AddressID
	from @PersonID T1
		join tblPerson T2 on T1.PersonID = T2.PersonID
	union select T2.Shipto_AddressID
	from tblCustomer T1
		join tblOrderHeader T2 on T1.CustomerID = T2.CUstomerID
	where T1.OwnerID = @OwnerID

	delete from T1
	from tblSequenceProduct T1
		join tblSequence T2 on T1.SequenceID = T2.SequenceID
	where T2.OwnerID = @OwnerID

	delete from T1
	from tblSequenceOrderStepProduct T1
		join tblSequenceStepProduct T2 on T1.SequenceStepProductID = T2.SequenceStepProductID
		join tblSequenceStep T3 on T2.SequenceStepID = T3.SequenceStepID
		join tblSequence T4 on T3.SequenceID = T4.SequenceID
	where T4.OwnerID = @OwnerID

	delete from T1
	from tblSequenceStepProduct T1
		join tblSequenceStep T2 on T1.SequenceStepID = T2.SequenceStepID
		join tblSequence T3 on T2.SequenceID = T3.SequenceID
	where T3.OwnerID = @OwnerID

	update tblOrderHeader set SequenceOrderStepID = null where OwnerID = @OwnerID

	delete from T1
	from tblSequenceOrderStep T1
		join tblSequenceStep T2 on T1.SequenceStepID = T2.SequenceStepID
		join tblSequence T3 on T2.SequenceID = T3.SequenceID
	where T3.OwnerID = @OwnerID

	delete from T1
	from tblSequenceStep T1
		join tblSequence T2 on T1.SequenceID = T2.SequenceID
	where T2.OwnerID = @OwnerID

	delete from T1
	from tblSequenceOrder T1
		join tblSequence T2 on T1.SequenceID = T2.SequenceID
	where T2.OwnerID = @OwnerID

	delete from tblSequence
	where OwnerID = @OwnerID

	delete from T1
	from tblProductComponent T1
		join tblProduct T2 on T1.ProductID = T2.ProductID
	where T2.OwnerID = @OwnerID

	delete from T1
	from tblProductPricing T1
		join tblProduct T2 on T1.ProductID = T2.ProductID
	where T2.OwnerID = @OwnerID

	delete from T1
	from tblInventoryTransactionHistory T1
		join tblInventoryTransaction T2 on T1.InventoryTransactionID = T2.InventoryTransactionID
		join tblInventory T3 on T2.InventoryID = T3.InventoryID
		join tblProduct T4 on T3.ProductID = T4.ProductID
	where T4.OwnerID = @OwnerID

	delete from T1
	from tblInventoryTransactionHistory T1
		join tblInventoryTransaction T2 on T1.InventoryTransactionID = T2.InventoryTransactionID
		join tblPackage T3 on T2.PackageID = T3.PackageID
	where T3.OwnerID = @OwnerID

	delete from T1
	from tblInventoryTransaction T1
		join tblInventory T3 on T1.InventoryID = T3.InventoryID
		join tblProduct T4 on T3.ProductID = T4.ProductID
	where T4.OwnerID = @OwnerID

	delete from T1
	from tblInventoryTransaction T1
		join tblPackage T2 on T1.PackageID = T2.PackageID
	where T2.OwnerID = @OwnerID

	delete from T1
	from tblInventoryCost T1
		join tblInventory T3 on T1.InventoryID = T3.InventoryID
		join tblProduct T4 on T3.ProductID = T4.ProductID
	where T4.OwnerID = @OwnerID

	delete from T1
	from tblInventory T1
		join tblProduct T4 on T1.ProductID = T4.ProductID
	where T4.OwnerID = @OwnerID

	declare @TransactionID table (TransactionID int primary key)

	insert into @TransactionID ( TransactionID )
	select T1.TransactionID 
	from tblTransaction T1
		join tblOrderHeader T2 on T1.OrderID = T2.OrderID where T2.OwnerID = @OwnerID

	insert into @TransactionID ( TransactionID )
	select T1.TransactionID 
	from tblRefund T1
		join tblOrderHeader T2 on T1.OrderID = T2.OrderID
		left outer join @TransactionID T3 on T1.TransactionID = T3.TransactionID
	where T2.OwnerID = @OwnerID
		AND t1.TransactionID IS NOT NULL
		and T3.TransactionID is null

	delete from T1
	from tblTransactionDetail T1
		join @TransactionID T2 on T1.TransactionID = T2.TransactionID

	delete from T2
	from tblRefund T2 
		join tblOrderHeader T3 on T2.OrderID = T3.OrderID
	where T3.OwnerID = @OwnerID

	delete from T2
	from tblOrderTax T2 
		join tblOrderHeader T3 on T2.OrderID = T3.OrderID
	where T3.OwnerID = @OwnerID

	delete from T1
	from tblTransaction T1
		join @TransactionID T2 on T1.TransactionID = T2.TransactionID

	delete from tblOwnerTaxRate where OwnerID = @OwnerID

	delete from codeProductPricingClass where OwnerID = @OwnerID

	update tblOwner set CashAccountID = null,
		ProductRevenueAccountID = null,
		InventoryAccountID = null,
		CostOfGoodsSoldAccountID = null,
		CostOfGoodsAdjustmentAccountID = null,
		ShippingCostAccountID = null,
		ShippingRevenueAccountID = null,
		TaxRevenueAccountID = null,
		MiscExpenseAccountID = null
	where OwnerID = @OwnerID

	delete from T1
	from tblReturnDetail T1
		join tblRMAReturn T2 on T1.RMAReturnID = T2.RMAREturnID
		join tblOrderHeader T3 on T2.OrderID = T3.OrderID
		join tblCustomer T4 on T3.CustomerID = T4.CustomerID
	where T4.OwnerID = @OwnerID

	delete from T2
	from tblRMAReturn T2 
		join tblOrderHeader T3 on T2.OrderID = T3.OrderID
		join tblCustomer T4 on T3.CustomerID = T4.CustomerID
	where T4.OwnerID = @OwnerID

	delete from T1
	from tblOrderDetail T1
		join tblOrderHeader T2 on T1.OrderID = T2.OrderID
		join tblCustomer T3 on T2.CUstomerID = T3.CustomerID
	where T3.OwnerID = @OwnerID

	delete from T1
	from tblOrderSourceID T1
	where T1.OwnerID = @OwnerID

	delete from T1
	from tblCustomerSourceID T1
	where T1.OwnerID = @OwnerID

	delete from T2
	from tblCustomer T1
		join tblOrderHeader T2 on T1.CustomerID = T2.CUstomerID
	where T1.OwnerID = @OwnerID

	declare @PrintBatchID table ( PrintBatchID int primary key )
	insert into @PrintBatchID ( PrintBatchID ) select distinct PrintBatchID from tblPackage where OwnerID = @OwnerID and PrintBatchID is not null

	delete from tblPackage where OwnerID = @OwnerID

	delete from T1
	from tblPrintBatch T1
		join @PrintBatchID T2 on T1.PrintBatchID = T2.PrintBatchID

	delete from tblCustomer
	where OwnerID = @OwnerID

	delete from tblProduct
	where OwnerID = @OwnerID

	delete from tblProductGroup
	where OwnerID = @OwnerID

	delete from T1
	from tblContactPoint T1
		join @PersonID T2 on T1.PersonID = T2.PersonID

	delete from tblAccount where OwnerID = @OwnerID

	delete from T2
	from @PersonID T1
		join tblPerson T2 on T1.PersonID = T2.PersonID

	delete from T2
	from @AddressID T1
		join tblAddress T2 on T1.AddressID = T2.AddressID

	delete from tblPromotion where OwnerID = @OwnerID

	delete from codeIDSource where OwnerID = @OwnerID

	delete from tblAccount where OwnerID = @OwnerID

	delete from tblMiscDef where OwnerID = @OwnerID

	delete from tblOwner where OwnerID = @OwnerID
END
